package com.recipes.meg;

import java.util.ArrayList;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDoneException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.widget.Toast;


public class DatabaseHelper extends SQLiteOpenHelper {
	// Make strings to hold the path to the database and the database name
	private static final String DB_PATH = "/data/data/com.recipes.meg/databases/";
	private static final String DB_NAME = "recipes.db";
	
	// Create variables to hold the context
	Context myContext;
	
	// Make constructor
	public DatabaseHelper(Context context){
		// Call super function and set current context
		super(context, DB_NAME, null, 1);
		myContext = context;
	}
	
	// Override onCreate function
	@Override
	public void onCreate(SQLiteDatabase db){
		// Add teams table
		try{
			db.execSQL("CREATE TABLE IF NOT EXISTS recipes (_id INTEGER PRIMARY KEY, name TEXT, instructions TEXT, description TEXT);");
		}catch (SQLException e){
			// If exception was thrown give an error message
			Toast.makeText(myContext, "Error creating recipes table in database", Toast.LENGTH_SHORT).show();
		}
	}
	
	// Override onUpgrade function
	@Override
	public void onUpgrade(SQLiteDatabase database, int old_version, int new_version){
	}
	
	// Create function to add a recipe to the database
	public int create(SQLiteDatabase db, String name, ArrayList<String> ingredients){
		// Check if db is non null
		if(db == null){
			// If it is give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to create new recipe", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare NULL string to insert into database as place holder for instructions
		String instructions = "NULL";
		
		// Prepare NULL string to insert into database as place holder for description
		String description = "NULL";
		
		// Get number of rows in the database
		int num_rows = getNumRows(db);
		
		// Prepare sqlite statement to insert new entry into recipes table
		SQLiteStatement statement = db.compileStatement("INSERT INTO recipes VALUES(?, ?, ?, ?);");
		
		// Bind next id to first position
		statement.bindLong(1, ++num_rows);
		
		// Bind the name to second position
		statement.bindString(2, name);
		
		// Bind instructions to third position
		statement.bindString(3, instructions);
		
		// Bind description to fourth position
		statement.bindString(4, description);
	
		// Initialize result of querry to -1
		int result = -1;
		
		// Execute insert and save return value
		try{
			result = (int) statement.executeInsert();
		} catch(SQLException e){
			// If exception thrown sql string was invalid so give an error message and leave
				// result as -1
			Toast.makeText(myContext, "Error: Insert statement invalid", Toast.LENGTH_SHORT).show();
		}
		
		// Check if insert returned negative
		if(result < 0){
			// If so give an error message and return
			Toast.makeText(myContext, "Error: Failed to insert new row into recipes table", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare string to be table name for this recipes ingredients table consisting of
			// ing appended with its id number
		String table_name = String.format("ing%d", num_rows);
		
		// Prepare sqlite statement to create the ingredients table for this recipe
		statement = db.compileStatement(
				"CREATE TABLE IF NOT EXISTS "+table_name+" (_id INTEGER PRIMARY KEY, name TEXT);");
		
		// Execute statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If exception was thrown sql string was invalid so give an error message and 
				// return -1
			Toast.makeText(myContext, "Error: Statement to create ingredients table invalid", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Iterate through ingredients array
		for(int i = 0; i < ingredients.size(); i++){
			// Prepare sqlite statement to insert this ingredient into ingredients table
			statement = db.compileStatement("INSERT INTO "+table_name+" VALUES(?, ?);");
		
			// Bind id value for this ingredient to first spot
			statement.bindLong(1, i+1);
			
			// Bind name of ingredient to second spot
			statement.bindString(2, ingredients.get(i));
			
			// Initialize result of querry to -1
			result = -1;
			
			// Try to execute insert
			try{
				result = (int) statement.executeInsert();
			} catch(SQLException e){
				// If exception was thrown then sql statemens was invalid so give an 
					// error message and leave result as -1
				Toast.makeText(myContext, "Error: Statement to insert ingredient was invalid", Toast.LENGTH_SHORT).show();
			}
			
			// Check if result is negative
			if(result < 0){
				// If it is then give an error message and return -1
				Toast.makeText(myContext, "Error: Could ont insert ingredient", Toast.LENGTH_SHORT).show();
				return -1;
			}
		}
		
		// Return id of new recipe for success
		return num_rows;
	}
	
	// Make a function to update the description and instructions of a recipe
	public int create2(SQLiteDatabase db, int id, String instructions, String description){
		// Check if db is null
		if(db == null){
			// If it is then give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to add instructions and description", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare a statement to update the instructions of this recipe
		SQLiteStatement statement = db.compileStatement("UPDATE recipes SET instructions = ? WHERE _id = ?;");
		
		// Bind the new instructions to the first spot
		statement.bindString(1, instructions);
		
		// Bind the id of this recipe to the second spot
		statement.bindLong(2, id);
		
		// Try to execute the statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If an exception was thrown then the sql statement was invalid so give an error
				// message and return -1
			Toast.makeText(myContext, "Error: Statement to update instructions was invalid", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare a statement to update the description of this recipe
		statement = db.compileStatement("UPDATE recipes SET description = ? WHERE _id = ?;");
		
		// Bind the new description to the first spot
		statement.bindString(1, description);
		
		// Bind the id of this recipe to the second spot
		statement.bindLong(2, id);
		
		// Try to execute the statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If an exception was thrown then the sql statement was invalid so give an error
				// message and return -1
			Toast.makeText(myContext, "Error: Statement to update description was invalid", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Return 0 for success
		return 0;
	}
	
	// Make a function to check if a name already exists in the database
	public int duplicateName(SQLiteDatabase db, String name, int id){
		// Check if db is null
		if(db == null){
			// If so then give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to check for uniqueness of recipe name", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare sqlite statement to look for a recipe with given name in the database
		SQLiteStatement statement = db.compileStatement("SELECT _id FROM recipes WHERE name = ?;");
		
		// Bind given name to statement
		statement.bindString(1, name);
		
		// Initialize result to 0
		int result = 0;
		
		// Try to execute query
		try{
			result = (int) statement.simpleQueryForLong();
		} catch(SQLiteDoneException e){
			// If an exception was thrown then no row in the database has this name 
				// so return 0
			return 0;
		}
		
		// Check if id returned is the id of this recipe
		if(id == result){
			// If so then return 0 since this recipe can have an identical name as itself
			return 0;
		}
		
		// Return id of duplicate recipe
		return result;
	}
	
	// Make a function to find a recipe by name
	public ArrayList<Integer> findByName(SQLiteDatabase db, String name){
		// Initialize empty return set
		ArrayList<Integer> results = new ArrayList<Integer>();
		
		// Check if db is null
		if(db == null){
			// If so give an error message and return empty set
			Toast.makeText(myContext, "Error: Could not access database to search for recipe by name", Toast.LENGTH_SHORT).show();
			return results;
		}
		
		// Get number of rows in the database
		int num_rows = getNumRows(db);
		
		// Iterate through each row in the database
		for(int i = 1; i <= num_rows; i++){
			// Get the name of this recipe
			String this_name = getName(db, i);
			
			// Check if this_name contains name passed ignoring case and trimming input name
			if(this_name.toLowerCase().contains(name.toLowerCase().trim())){
				// If so then add this id to the result set
				results.add(i);
			}
		}
		
		// Return the result set
		return results;
	}
	
	// Make a function to search for recipes with a given ingredient
	public ArrayList<Integer> findByIng(SQLiteDatabase db, String ing){
		// Initialize array of return values to be empty
		ArrayList<Integer> results = new ArrayList<Integer>();
		
		// Check if db is null
		if(db == null){
			// If so then give an error message and return empty result set
			Toast.makeText(myContext, "Error: Could not access database to search for recipe by ingredient", Toast.LENGTH_SHORT).show();
			return results;
		}
	
		// Get number of rows from the database
		int num_rows = getNumRows(db);
		
		// Iterate for each row in the recipes table
		for(int i = 1; i <= num_rows; i++){
			// Generate the string for this recipes ingredients table name
			String table_name = String.format("ing%d", i);
			
			// Prepare sqlite statement to check if given ingredient is in this recipes
				// ingredients table
			SQLiteStatement statement = db.compileStatement("SELECT _id FROM "+table_name+" WHERE name LIKE ?;");
		
			// Generate string to use as wildcard search with trimmed input name
			String search_term = String.format("%%%s%%", ing.trim());
			
			// Bind search_term to first spot 
			statement.bindString(1, search_term);
			
			// Try to execute query
			try{
				int id = (int) statement.simpleQueryForLong();
				
				// If a row was returned then this recipe has the given ingredient so 
					// add this recipe id to the result set
				results.add(i);
			} catch(SQLiteDoneException e){
				// If exception was thrown then ingredient was not in this table 
			}
		}
		
		// Return result set
		return results;
	}
	
	// Define function to return name of a recipe given its id
	public String getName(SQLiteDatabase db, int id){
		// Initialize blank string to return
		String result = "";
		
		// Check if db is null
		if(db == null){
			// If it does then give an error message and return an emtpy name
			Toast.makeText(myContext, "Error: Could not access database to retrieve recipes name", Toast.LENGTH_SHORT).show();
			return result;
		}
		
		// Prepare sqlite statement to get name from database
		SQLiteStatement statement = db.compileStatement("SELECT name FROM recipes WHERE _id = ?;");
		
		// Bind recipe id to statement
		statement.bindLong(1, id);
		
		// Try to execute query
		try{
			result = statement.simpleQueryForString();
		} catch(SQLiteDoneException e){
			// If exception was thrown then no recipe with this id is in the table
		}
		
		// Return result
		return result;
	}
	
	// Define a function to return the number of recipes/rows in the database
	public int getNumRows(SQLiteDatabase db){
		// Check if db is null
		if(db == null){
			// If it is then give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to get number of rows in the database", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Prepare sqlite statement to get number of entries in recipes table
		SQLiteStatement statement = db.compileStatement("SELECT COUNT(*) FROM recipes;");
		
		// Initialize number of rows to 0
		int num_rows = 0;
		
		// Execute query to get number of rows in recipes table
		try{
			num_rows = (int) statement.simpleQueryForLong();
		} catch(SQLiteDoneException e){
			// If this returned 0 then there are no rows in the table
		}
		
		// Return the number of rows
		return num_rows;
	}
	
	// Create a function to get the number of ingredients for a recipe
	public int getNumIng(SQLiteDatabase db, int id){
		// Check if db is null
		if(db == null){
			// If so give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to get number of ingredients", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Format string corresponding to this recipes ingredients table name
		String table = String.format("ing%d", id);
		
		// Prepare sqlite statement to get number of ingredients for this recipe
		SQLiteStatement statement = db.compileStatement("SELECT COUNT(*) FROM "+table+";");
		
		// Initialize num_ing to 0
		int num_ing = 0;
		
		// Try to execute query
		try{
			num_ing = (int) statement.simpleQueryForLong();
		} catch(SQLiteDoneException e){
			// If an exception was thrown no rows are in ingredients table
		}
		
		// Return num_ing
		return num_ing;
	}
	
	// Create a function to get the ingredients for a recipe
	public ArrayList<String> getIng(SQLiteDatabase db, int id){
		// Initialize empty result list
		ArrayList<String> results = new ArrayList<String>();
		
		// Check if db is null
		if(db == null){
			// If so give an error message and return empty result list
			Toast.makeText(myContext, "Error: Could not access database to get ingredients", Toast.LENGTH_SHORT).show();
			return results;
		}
		
		// Get number of ingredients for this recipe
		int num_ing = getNumIng(db, id);
		
		// Format string corresponding to this recipes ingredient table name
		String table = String.format("ing%d", id);
		
		// Iterate once for each ingredient
		for(int i = 1; i <= num_ing; i++){
			// Prepare sqlite statement to get this ingredients name
			SQLiteStatement statement = db.compileStatement("SELECT name FROM "+table+" WHERE _id = ?;");
			
			// Bind current ingredient number to first spot
			statement.bindLong(1, i);
			
			// Initialize ing name to blank
			String ing = "";
			
			// Try to execute query
			try{
				ing = statement.simpleQueryForString();
			} catch(SQLiteDoneException e){
				// If an exception was thrown then no results were returned
			}
			
			// Check if ing is not blank
			if(!ing.equalsIgnoreCase("")){
				// If not then add it to the result list
				results.add(ing);
			}
		}
		
		// Return results
		return results;
	}
	
	// Define a function to get the instructions for a recipe
	public String getInstructions(SQLiteDatabase db, int id){
		// Initialize blank string
		String result = "";
		
		// Check if db is null
		if(db == null){
			// If so then give an error message and return blank string
			Toast.makeText(myContext, "Error: Could not access database to get ingredient name", Toast.LENGTH_SHORT).show();
			return result;
		}
		
		// Prepare sqlite statement to get the instructions for this recipe
		SQLiteStatement statement = db.compileStatement("SELECT instructions FROM recipes WHERE _id = ?;");
		
		// Bind this recipes id to the statement
		statement.bindLong(1, id);
		
		// Try to execute the statement
		try{
			result = statement.simpleQueryForString();
		} catch(SQLiteDoneException e){
			// If an exception was thrown then no results were found
		}
		
		// Return result
		return result;
	}
	
	// Define function to get the description of a recipe
	public String getDescription(SQLiteDatabase db, int id){
		// Initialize result string to blank
		String result = "";
		
		// Check if db is null
		if(db == null){
			// If so give an error message and return empty string
			Toast.makeText(myContext, "Error: Could not access database to get recipe description", Toast.LENGTH_SHORT).show();
			return result;
		}
		
		// Prepare sqlite statement to get description for this recipe
		SQLiteStatement statement = db.compileStatement("SELECT description FROM recipes WHERE _id = ?;");
		
		// Bind this recipes id to statement
		statement.bindLong(1, id);
		
		// Try to execute the statement
		try{
			result = statement.simpleQueryForString();
		} catch(SQLiteDoneException e){
			// If an exception was thrown then no results were returned
		}
		
		// Return result
		return result;
	}
	
	// Create function to update the name of a recipe in the database
	public boolean updateName(SQLiteDatabase db, int id, String name){
		// Check if db is null
		if(db == null){
			// If so give an error message and return false
			Toast.makeText(myContext, "Error: Could not access database to change name of recipe", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Prepare sqlite statement to change name of this recipe
		SQLiteStatement statement = db.compileStatement("UPDATE recipes SET name = ? WHERE _id = ?;");
		
		// Bind name to first spot
		statement.bindString(1, name);
		
		// Bind id to second spot
		statement.bindLong(2, id);
		
		// Try to execute statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If exception was thrown then give an error message and return false
			Toast.makeText(myContext, "Error: Could not update name of recipe in database", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Return true for success
		return true;
	}
	
	// Create a function to update the name and ingredients of a recipe
	public boolean update(SQLiteDatabase db, int id, String name, ArrayList<String> ingredients){
		// Check if db is null
		if(db == null){
			// If so give an error message and return false
			Toast.makeText(myContext, "Error: Could not access database to update name and ingredients", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Try to update name of this recipe
		boolean updated = updateName(db, id, name);
		
		// Check if name failed to be updated
		if(!updated){
			// If so return false
			return false;
		}
		
		// Format string corresponding to this recipes ingredients table name
		String table = String.format("ing%d", id);
		
		// Try to execute sql to remove ingredients table for this recipe
		try{
			db.execSQL("DROP TABLE IF EXISTS "+table+";");
		} catch (SQLException e){
			// If exception was thrown then sql string was invalid so give an error message 
				// and return false
			Toast.makeText(myContext, "Error: Could not delete old ingredients table for this recipe", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Get number of new ingredients
		int num_ing = ingredients.size();
		

		// Prepare sqlite statement to create the ingredients table for this recipe
		SQLiteStatement statement = db.compileStatement(
				"CREATE TABLE IF NOT EXISTS "+table+" (_id INTEGER PRIMARY KEY, name TEXT);");
		
		// Execute statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If exception was thrown sql string was invalid so give an error message and 
				// return false
			Toast.makeText(myContext, "Error: Statement to create ingredients table invalid", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Iterate through ingredients array
		for(int i = 0; i < ingredients.size(); i++){
			// Prepare sqlite statement to insert this ingredient into ingredients table
			statement = db.compileStatement("INSERT INTO "+table+" VALUES(?, ?);");
		
			// Bind id value for this ingredient to first spot
			statement.bindLong(1, i+1);
			
			// Bind name of ingredient to second spot
			statement.bindString(2, ingredients.get(i));
			
			// Initialize result of querry to -1
			int result = -1;
			
			// Try to execute insert
			try{
				result = (int) statement.executeInsert();
			} catch(SQLException e){
				// If exception was thrown then sql statement was invalid so give an 
					//error message and leave result as -1
				Toast.makeText(myContext, "Error: Statement to insert new ingredient was invalid", Toast.LENGTH_SHORT).show();
			}
			
			// Check if result is negative
			if(result < 0){
				// If it is then give an error message and return false
				Toast.makeText(myContext, "Error: Could not insert new ingredient", Toast.LENGTH_SHORT).show();
				return false;
			}
		}
		
		// Return true
		return true;
	}
	
	// Create function to update the instructions of a recipe in the database
	public boolean updateInstructions(SQLiteDatabase db, int id, String instructions){
		// Check if db is null
		if(db == null){
			// If so give an error message and return false
			Toast.makeText(myContext, "Error: Could not access database to change instructions of recipe", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Prepare sqlite statement to change description of this recipe
		SQLiteStatement statement = db.compileStatement("UPDATE recipes SET instructions = ? WHERE _id = ?;");
		
		// Bind instructions to first spot
		statement.bindString(1, instructions);
		
		// Bind id to second spot
		statement.bindLong(2, id);
		
		// Try to execute statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If exception was thrown then give an error message and return false
			Toast.makeText(myContext, "Error: Could not update instructions of recipe in database", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Return true for success
		return true;
	}
	
	// Create function to update the description of a recipe in the database
	public boolean updateDescription(SQLiteDatabase db, int id, String description){
		// Check if db is null
		if(db == null){
			// If so give an error message and return false
			Toast.makeText(myContext, "Error: Could not access database to change description of recipe", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Prepare sqlite statement to change description of this recipe
		SQLiteStatement statement = db.compileStatement("UPDATE recipes SET description = ? WHERE _id = ?;");
		
		// Bind description to first spot
		statement.bindString(1, description);
		
		// Bind id to second spot
		statement.bindLong(2, id);
		
		// Try to execute statement
		try{
			statement.execute();
		} catch(SQLException e){
			// If exception was thrown then give an error message and return false
			Toast.makeText(myContext, "Error: Could not update description of recipe in database", Toast.LENGTH_SHORT).show();
			return false;
		}
		
		// Return true for success
		return true;
	}
	
	// Create function to delete a recipe from the database
	public int deleteRecipe(SQLiteDatabase db, int id){
		// Check if db is null
		if(db == null){
			// If so then give an error message and return -1
			Toast.makeText(myContext, "Error: Could not access database to delete recipe", Toast.LENGTH_SHORT).show();
			return -1;
		}
		
		// Format string corresponding to ingredients table for this recipe
		String table = String.format("ing%d", id);
		
		// Try to execute sql to remove ingredients table for this recipe
		try{
			db.execSQL("DROP TABLE IF EXISTS "+table+";");
		} catch (SQLException e){
			// If exception was thrown then sql string was invalid so give an error message 
				// and return -1
			Toast.makeText(myContext, "Error: Could not delete ingredients table for this recipe", Toast.LENGTH_SHORT).show();
			return -1;
		}

		// Get number of rows in the recipes table
		int num_rows = getNumRows(db);
		
		// Iterate for each row after this one in the recipes table
		for(int i = id + 1; i <= num_rows; i++){
			// Get the name of current recipe
			String name = getName(db, i);
			
			// Get the instructions for current recipe
			String instructions = getInstructions(db, i);
			
			// Get the description for current recipe
			String description = getDescription(db, i);
		
			// Update name of previous recipe to be name of current recipe
			if(!updateName(db, i-1, name)){
				// If this fails then return -2
				return -2;
			}
			
			// Update instructions of previous recipe to be instructions of current recipe
			if(!updateInstructions(db, i-1, instructions)){
				// If this fails then return -2
				return -2;
			}
			
			// Update description of previous recipe to be description of current recipe
			if(!updateDescription(db, i-1, description)){
				// If this fails then return -2
				return -2;
			}
			
			// Prepare string corresponding to this recipes current table name
			String old_name = String.format("ing%d", i);
			
			// Prepare string corresponding to this recipes new table name
			String new_name = String.format("ing%d", i-1);
			
			// Prepare sqlite statement to change name of this recipes ingredients table
			SQLiteStatement statement = db.compileStatement("ALTER TABLE "+old_name+
					" RENAME TO "+new_name+";");
			
			// Try to execute statement
			try{
				statement.execute();
			} catch(SQLException e){
				// If exception was thrown then give an error message and return -2
				Toast.makeText(myContext, "Error: Could not rename ingredients table for recipe", Toast.LENGTH_SHORT).show();
				return -2;
			}
		}
		
		// Delete the last entry from the recipes table and save the result
		int result = db.delete("recipes", "_id = ?", new String[]{String.format("%d", num_rows)});
		
		// Check if too many rows were deleted from recipes table
		if(result > 1){
			// If so then give an error message and return -2
			Toast.makeText(myContext, "Error: Deleted multiple recipes from database unexpectedly", Toast.LENGTH_SHORT).show();
			return -2;
		}
		
		// Else check if no rows were deleted
		else if(result < 1){
			// If so then give an error message and return -3
			Toast.makeText(myContext, "Error: Could not delete this entry from recipes table", Toast.LENGTH_SHORT).show();
			return -3;
		}
		
		// Return 0 for success
		return 0;
	}
}
